﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Administration.Catalog
{
    public partial class recDebtorSectorRepo : ISearchable<recDebtorSector>
    {

        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM master.recDebtorSector", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM master.recDebtorSector", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM master.recDebtorSector", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.recDebtorSector", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM master.recDebtorSector", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM master.recDebtorSector", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recDebtorSector> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recDebtorSector.DebtorSectorID
                                                , recDebtorSector.DebtorID
                                                , recDebtorSector.SectorID
                                                , recDebtorSector.SubSectorID
                                                , recDebtorSector.SubSubSectorID
                                                , recDebtorSector.IsActive
                                                , recDebtorSector.CreatedDate
                                                , recDebtorSector.CreatedBy
                                                , recDebtorSector.UpdatedDate
                                                , recDebtorSector.UpdatedBy 
                                                , recDebtor.DebtorName
                                                , recSector.SectorName
                                                , recSubSector.SubSectorName
                                                , recSubSubSector.SubSubSectorName");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      master.recDebtorSector INNER JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID 
                                                INNER JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID
                                                INNER JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID
                                                INNER JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID");
                    sbQuery.AppendFormat("    {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector(true);
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recDebtorSector.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorID].ToString());
                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.IsActive = dr[recDebtorSector.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtorSector.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtorSector.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtorSector.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtorSector.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtorSector.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.UpdatedBy].ToString();
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDebtorSector> GetListByDebtor(int debtorID)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.Append(@"
                                    select 
	                                    recDebtorSector.DebtorSectorID, 
	                                    recSector.SectorID, 
	                                    recSector.SectorCode, 
	                                    recSector.SectorName, 
	                                    recDebtorSector.SubSectorID,
	                                    recSubSector.SubSectorCode,  
	                                    recSubSector.SubSectorName, 
	                                    recDebtorSector.SubSubSectorID, 
	                                    recSubSubSector.SubSubSectorCode,	
	                                    recSubSubSector.SubSubSectorName,
	                                    recDebtorSector.DebtorID,
	                                    recDebtorSector.IsActive 
                                    from 
	                                    master.recDebtorSector
	                                    inner join master.recSector on (master.recDebtorSector.SectorID = master.recSector.SectorID)
	                                    inner join master.recSubSector on (master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID)
	                                    inner join master.recSubSubSector on (master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID)
                                    WHERE master.recDebtorSector.DebtorID = @DebtorID ");

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorID;

                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector(true);
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recDebtorSector.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorID].ToString());

                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.IsActive = dr[recDebtorSector.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtorSector.Fields.IsActive].ToString());

                                t.recSector.SectorID = dr[recSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recSector.Fields.SectorID].ToString());
                                t.recSubSector.SubSectorID = dr[recSubSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSubSector.Fields.SubSectorID].ToString());
                                t.recSubSubSector.SubSubSectorID = dr[recSubSubSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSubSubSector.Fields.SubSubSectorID].ToString());

                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();

                                t.recSector.SectorCode = dr[recSector.Fields.SectorCode] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorCode].ToString();
                                t.recSubSector.SubSectorCode = dr[recSubSector.Fields.SubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorCode].ToString();
                                t.recSubSubSector.SubSubSectorCode = dr[recSubSubSector.Fields.SubSubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorCode].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDebtorSector> GetListWithoutDebtor(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"       recSector.SectorID
                                                , recSubSector.SubSectorID
                                                , recSubSubSector.SubSubSectorID
                                                , recSector.SectorName
                                                , recSubSector.SubSectorName
                                                , recSubSubSector.SubSubSectorName");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"       
                                                master.recSector INNER JOIN master.recSubSector ON recSector.SectorID = recSubSector.SectorID
                                                INNER JOIN master.recSubSubSector ON recSubSector.SubSectorID = recSubSubSector.SubSectorID");
                    sbQuery.AppendFormat("    {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector(true);
                                t.SectorID = dr[recSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recSubSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recSubSubSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public recDebtorSector GetByNPWPAndSector(string npwp, int sectorId, int subSectorId, int subSubSectorId)
        {
            recDebtorSector t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select mrd.NPWP,mrds.SectorID,mrds.SubSectorID,mrds.SubSubSectorID,mrds.DebtorSectorID from master.recDebtor mrd {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recDebtorSector mrds on mrds.DebtorID=mrd.DebtorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where mrd.NPWP = @NPWP and mrds.SectorID=@SectorId and mrds.SubSectorID=@SubSectorId and mrds.SubSubSectorID=@SubSubSectorId {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = npwp;
                    cmd.Parameters.Add("@SectorId", SqlDbType.Int).Value = sectorId;
                    cmd.Parameters.Add("@SubSectorId", SqlDbType.Int).Value = subSectorId;
                    cmd.Parameters.Add("@SubSubSectorId", SqlDbType.Int).Value = subSubSectorId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtorSector(true);
                                t.recDebtor.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recDebtorSector GetByNPWPAndSectorUnion(string npwp, int sectorId, int subSectorId, int subSubSectorId)
        {
            recDebtorSector t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select gabungan.NPWP as NPWP,gabungan.SectorID as SectorID,gabungan.SubSectorID as SubSectorID,gabungan.SubSubSectorID as SubSubSectorID from ( {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select mrd.NPWP,mrds.SectorID,mrds.SubSectorID,mrds.SubSubSectorID from master.recDebtor mrd {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recDebtorSector mrds on mrds.DebtorID=mrd.DebtorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where mrd.NPWP = @NPWP and mrds.SectorID=@SectorId and mrds.SubSectorID=@SubSectorId and mrds.SubSubSectorID=@SubSubSectorId {0}", Environment.NewLine);
                    sbQuery.AppendFormat("union {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select trr.NPWP,trsr.SectorID,trsr.SubSectorID,trsr.SubSubSectorID from transactions.recRegistration trr {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join transactions.recSectorRegistration trsr on trsr.RegistrationID=trr.RegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where trr.NPWP=@NPWP and trsr.SectorID=@SectorId and trsr.SubSectorID=@SubSectorId and trsr.SubSubSectorID=@SubSubSectorId) gabungan {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = npwp;
                    cmd.Parameters.Add("@SectorId", SqlDbType.Int).Value = sectorId;
                    cmd.Parameters.Add("@SubSectorId", SqlDbType.Int).Value = subSectorId;
                    cmd.Parameters.Add("@SubSubSectorId", SqlDbType.Int).Value = subSubSectorId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtorSector(true);
                                t.recDebtor.NPWP = dr["NPWP"] == DBNull.Value ? string.Empty : dr["NPWP"].ToString();
                                t.SectorID = dr["SectorID"] == DBNull.Value ? 0 : int.Parse(dr["SectorID"].ToString());
                                t.SubSectorID = dr["SubSectorID"] == DBNull.Value ? 0 : int.Parse(dr["SubSectorID"].ToString());
                                t.SubSubSectorID = dr["SubSubSectorID"] == DBNull.Value ? 0 : int.Parse(dr["SubSubSectorID"].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public List<recDebtorSector> GetGroupSubSectorList(int debtorid)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DISTINCT SubSectorID FROM master.recDebtorSector WHERE DebtorID = @DebtorID ORDER BY SubSectorID ASC";
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorid;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector();
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDebtorSector> GetGroupSubSectorList(int debtorid, string subSubSectorIds)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("SELECT DISTINCT master.recDebtorSector.SubSectorID FROM master.recDebtorSector ");
                    sbQuery.AppendFormat("inner join master.recSubSector on master.recSubSector.SubSectorID=master.recDebtorSector.SubSectorID ");
                    sbQuery.AppendFormat("inner join master.recSubSubSector on master.recSubSubSector.SubSubSectorID=master.recDebtorSector.SubSubSectorID ");
                    sbQuery.AppendFormat("where master.recDebtorSector.DebtorID={0} and master.recSubSubSector.SubSubSectorID in ({1}) ORDER BY master.recDebtorSector.SubSectorID ASC ", debtorid, subSubSectorIds);
                    //cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorid;
                    //cmd.Parameters.Add("@subSubSectorId", SqlDbType.VarChar).Value = subSubSectorIds;
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector();
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDebtorSector> GetGroupSubSectorList(int debtorid, int subSectorId, string subSubSectorIds)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("SELECT DISTINCT master.recDebtorSector.SubSubSectorID,master.recDebtorSector.DebtorSectorID FROM master.recDebtorSector ");
                    sbQuery.AppendFormat("inner join master.recSubSector on master.recSubSector.SubSectorID=master.recDebtorSector.SubSectorID ");
                    sbQuery.AppendFormat("inner join master.recSubSubSector on master.recSubSubSector.SubSubSectorID=master.recDebtorSector.SubSubSectorID ");
                    sbQuery.AppendFormat("where master.recDebtorSector.DebtorID={0} and master.recSubSector.SubSectorID={1} and  master.recSubSubSector.SubSubSectorID in ({2}) ORDER BY master.recDebtorSector.SubSubSectorID ASC ", debtorid, subSectorId, subSubSectorIds);
                    //cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorid;
                    //cmd.Parameters.Add("@subSubSectorId", SqlDbType.VarChar).Value = subSubSectorIds;
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector();
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDebtorSector> GetListByDebtor_Sector_SubSector_SubSubSector(int debtorID, int sectorID, int subsectorID, int subsubSectorID)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DebtorSectorID, DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recDebtorSector WHERE DebtorID=@DebtorID AND SectorID=@SectorID AND SubSectorID=@SubSectorID AND SubSubSectorID=@SubSubSectorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorID;
                    cmd.Parameters.Add("@SectorID", SqlDbType.Int).Value = sectorID;
                    cmd.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = subsectorID;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subsubSectorID;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector();
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recDebtorSector.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorID].ToString());
                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.IsActive = dr[recDebtorSector.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtorSector.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtorSector.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtorSector.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtorSector.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtorSector.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDebtorSector> GetListByDebtorCorporateAction(int debtorID, string pasarModalCode, string subSectorCodes)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.Append(@"
                                    Select
	                                    recDebtorSector.DebtorSectorID, 
	                                    recDebtorSector.DebtorID,
	                                    recSector.SectorID, 
	                                    recSector.SectorCode, 
	                                    recSector.SectorName, 
	                                    recDebtorSector.SubSectorID,
	                                    recSubSector.SubSectorCode,  
	                                    recSubSector.SubSectorName, 
	                                    recDebtorSector.SubSubSectorID, 
	                                    recSubSubSector.SubSubSectorCode,	
	                                    recSubSubSector.SubSubSectorName,
	                                    recDebtorSector.IsActive 
                                    from 
	                                    master.recDebtorSector
                                        inner join master.recSector on (recDebtorSector.SectorID = recSector.SectorID AND recSector.SectorCode = '" + pasarModalCode + @"')
	                                    inner join master.recSubSector on (recDebtorSector.SubSectorID = recSubSector.SubSectorID AND recSubSector.SubSectorCode IN (" + subSectorCodes + @"))
	                                    inner join master.recSubSubSector on (recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID)
                                    WHERE master.recDebtorSector.DebtorID = @DebtorID");

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorID;

                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector(true);
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recDebtorSector.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorID].ToString());

                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.IsActive = dr[recDebtorSector.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtorSector.Fields.IsActive].ToString());

                                t.recSector.SectorID = dr[recSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recSector.Fields.SectorID].ToString());
                                t.recSubSector.SubSectorID = dr[recSubSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSubSector.Fields.SubSectorID].ToString());
                                t.recSubSubSector.SubSubSectorID = dr[recSubSubSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSubSubSector.Fields.SubSubSectorID].ToString());

                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();

                                t.recSector.SectorCode = dr[recSector.Fields.SectorCode] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorCode].ToString();
                                t.recSubSector.SubSectorCode = dr[recSubSector.Fields.SubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorCode].ToString();
                                t.recSubSubSector.SubSubSectorCode = dr[recSubSubSector.Fields.SubSubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorCode].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public recDebtorSector GetById(int debtorId, int subsubsectorId)
        {
            recDebtorSector t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DebtorSectorID, DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recDebtorSector WHERE DebtorID = @DebtorID AND SubSubSectorID = @SubSubSectorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorId;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subsubsectorId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtorSector();
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recDebtorSector.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorID].ToString());
                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.IsActive = dr[recDebtorSector.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtorSector.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtorSector.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtorSector.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtorSector.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtorSector.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.UpdatedBy].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public List<recDebtorSector> GetById(int debtorId, string subsubsectorIdS)
        {
            List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DebtorSectorID, DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recDebtorSector WHERE DebtorID = @DebtorID AND SubSubSectorID in("+ subsubsectorIdS +")";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorId;
                 //   cmd.Parameters.Add("@SubSubSectorID", SqlDbType.VarChar).Value = subsubsectorIdS;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = null;
                                t = new recDebtorSector();
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recDebtorSector.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorID].ToString());
                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.IsActive = dr[recDebtorSector.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtorSector.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtorSector.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtorSector.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtorSector.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtorSector.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }


        public List<recDebtorSector> GetListByID(int debtorSectorID)
        {
           List<recDebtorSector> tt = new List<recDebtorSector>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DebtorSectorID, DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recDebtorSector WHERE DebtorSectorID = @DebtorSectorID ";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorSectorID;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtorSector t = new recDebtorSector();
                                t.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recDebtorSector.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorID].ToString());
                                t.SectorID = dr[recDebtorSector.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recDebtorSector.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recDebtorSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.SubSubSectorID].ToString());
                                t.IsActive = dr[recDebtorSector.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtorSector.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtorSector.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtorSector.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtorSector.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtorSector.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtorSector.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtorSector.Fields.UpdatedBy].ToString();

                                tt.Add(t);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }


    }
}

